package com.you.shortlink.project.dao.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.you.shortlink.project.dao.entity.LinkAccessLogsDO;
import com.you.shortlink.project.dao.entity.LinkAccessStatsDO;
import com.you.shortlink.project.dto.req.ShortLinkGroupStatsReqDTO;
import com.you.shortlink.project.dto.req.ShortLinkStatsAccessRecordReqDTO;
import com.you.shortlink.project.dto.req.ShortLinkStatsReqDTO;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 监控日志访问持久层
 */
public interface LinkAccessLogsMapper extends BaseMapper<LinkAccessLogsDO> {

    /**
     * 记录短链接监控日志数据
     */
    @Insert("""
            INSERT INTO t_link_access_logs (full_short_url, gid, user, browser, os, ip, network, locale, device, create_time, update_time, del_flag) 
            VALUES (#{linkAccessLogs.fullShortUrl}, #{linkAccessLogs.gid}, #{linkAccessLogs.user}, #{linkAccessLogs.browser}, #{linkAccessLogs.os},
            #{linkAccessLogs.ip}, #{linkAccessLogs.network}, #{linkAccessLogs.locale}, #{linkAccessLogs.device}, NOW(), NOW(), 0);
            """)
    void shortLinkAccessLogs(@Param("linkAccessLogs") LinkAccessLogsDO linkAccessLogsDO);

    /**
     * 根据短链接获取指定日期内高频访问IP数据
     */
    @Select("""
            SELECT ip, COUNT(ip) AS `count` FROM t_link_access_logs WHERE full_short_url = #{param.fullShortUrl} AND gid = #{param.gid}
            AND create_time BETWEEN CONCAT(#{param.startDate}, ' 00:00:00') AND CONCAT(#{param.endDate}, ' 23:59:59')
            GROUP BY full_short_url, gid, ip
            ORDER BY `count` desc LIMIT 5;
            """)
    List<HashMap<String, Object>> listTopIpByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);

    /**
     * 根据短链接获取指定日期内新旧访客数据
     */
    @Select("""
            SELECT
            SUM(old_user) AS oldUserCnt,
            SUM(new_user) AS newUserCnt
            FROM (
                SELECT CASE WHEN COUNT(DISTINCT DATE(create_time)) > 1 THEN 1 ELSE 0 END AS old_user,
                CASE WHEN COUNT(DISTINCT DATE(create_time)) = 1 AND MAX(create_time) >= CONCAT(#{param.startDate}, ' 00:00:00') AND MAX(create_time) <= CONCAT(#{param.endDate}, ' 23:59:59') THEN 1 ELSE 0 END AS new_user
                FROM t_link_access_logs
                WHERE full_short_url = #{param.fullShortUrl} AND gid = #{param.gid}
                GROUP BY `user`
            ) AS user_counts;
            """)
    HashMap<String, Object> findUvTypeCntByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);

    /**
     * 根据短链接以及用户列表获取访问记录中的访客类型
     */
    @Select("""
            <script>
            SELECT `user`, 
            CASE WHEN MIN(create_time) BETWEEN #{startDate} AND #{endDate} THEN '新访客' ELSE '老访客' END AS uvType
            FROM t_link_access_logs WHERE full_short_url = #{fullShortUrl} AND gid = #{gid}
            <if test = "list != null and list.size() > 0">
                AND `user` IN
                <foreach item='item' index='index' collection='list' open='(' separator=',' close=')'>
                    #{item}
                </foreach>
            </if>
            GROUP BY `user`;
            </script>
            """)
    List<Map<String, Object>> selectUvTypeByUsers(
            @Param("fullShortUrl") String fullShortUrl,
            @Param("gid") String gid,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate,
            @Param("list") List<String> userAccessLogsList);

    /**
     * 根据分组标识用户列表获取分组访问记录中的访客类型
     */
    @Select("""
            <script>
            SELECT `user`, 
            CASE WHEN MIN(create_time) BETWEEN #{startDate} AND #{endDate} THEN '新访客' ELSE '老访客' END AS uvType
            FROM t_link_access_logs WHERE gid = #{gid}
            <if test = "list != null and list.size() > 0">
                AND `user` IN
                <foreach item='item' index='index' collection='list' open='(' separator=',' close=')'>
                    #{item}
                </foreach>
            </if>
            GROUP BY `user`;
            </script>
            """)
    List<Map<String, Object>> selectGroupUvTypeByUsers(
            @Param("gid") String gid,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate,
            @Param("list") List<String> userAccessLogsList);

    /**
     * 根据短链接分组获取指定日期内高频访问IP数据
     */
    @Select("""
            SELECT ip, COUNT(ip) AS `count` FROM t_link_access_logs WHERE gid = #{param.gid}
            AND create_time BETWEEN CONCAT(#{param.startDate}, ' 00:00:00') AND CONCAT(#{param.endDate}, ' 23:59:59')
            GROUP BY gid, ip
            ORDER BY `count` desc LIMIT 5;
            """)
    List<HashMap<String, Object>> listTopIpByShortLinkGroup(@Param("param") ShortLinkGroupStatsReqDTO requestParam);

    /**
     * 根据短链接分组获取指定日期内新旧访客数据
     */
    @Select("""
            SELECT
            SUM(old_user) AS oldUserCnt,
            SUM(new_user) AS newUserCnt
            FROM (
                SELECT CASE WHEN COUNT(DISTINCT DATE(create_time)) > 1 THEN 1 ELSE 0 END AS old_user,
                CASE WHEN COUNT(DISTINCT DATE(create_time)) = 1 AND MAX(create_time) >= CONCAT(#{param.startDate}, ' 00:00:00') AND MAX(create_time) <= CONCAT(#{param.endDate}, ' 23:59:59') THEN 1 ELSE 0 END AS new_user
                FROM t_link_access_logs
                WHERE gid = #{param.gid}
                GROUP BY `user`
            ) AS user_counts;
            """)
    HashMap<String, Object> findUvTypeCntByShortLinkGroup(@Param("param") ShortLinkGroupStatsReqDTO requestParam);

    /**
     * 根据短链接获取短链接统计信息
     */
    @Select("""
            SELECT full_short_url, gid, 
            COUNT(`user`) AS pv,
            COUNT(DISTINCT `user`) AS uv,
            COUNT(DISTINCT ip) AS uip
            FROM t_link_access_logs
            WHERE full_short_url = #{param.fullShortUrl} AND gid = #{param.gid} AND create_time BETWEEN #{param.startDate} AND #{param.endDate}
            GROUP BY full_short_url, gid;
            """)
    LinkAccessStatsDO findPvUvUipStatsByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);


    /**
     * 根据短链接分组获取短链接统计信息
     */
    @Select("""
            SELECT
            COUNT(`user`) AS pv,
            COUNT(DISTINCT `user`) AS uv,
            COUNT(DISTINCT ip) AS uip
            FROM t_link_access_logs
            WHERE gid = #{param.gid} AND create_time BETWEEN #{param.startDate} AND #{param.endDate}
            GROUP BY gid;
            """)
    LinkAccessStatsDO findPvUvUipStatsByShortLinkGroup(@Param("param") ShortLinkGroupStatsReqDTO requestParam);

}
